﻿//#undef MySql
//#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;
using System.Net.Mail;

using System.Data.OleDb;

using MySql.Data.MySqlClient;

/////////////// office2003 Excel 
using Excel = Microsoft.Office.Interop.Excel;


namespace ukeasbjWeb
{
    public partial class InforBeijing : System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);
            if (!IsPostBack)
            {	// 初始化页面
                Init_All();
            }

            if (!IsPostBack)
            {/*
                GridView1.DataSource = CreateDataSource();
                GridView1.DataBind();
              */
            }
        }

        /// <summary>
        /// 初始化： 页面
        /// </summary>
        private void Init_All()
        {
            if (Session["Office"].ToString().Trim() == "北京")
                Bt_IP_Beijing.Enabled = true;
            else
                Bt_IP_Beijing.Enabled = false;

        }

        /// <summary>
        /// 发送北京的动态IP
        /// </summary>
        private void Sendmail_IP_BJ()
        {
            try
            {
                // 服务器名，端口号
                System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(Common_SXD.smtp_ukeas, 25);
                smtp.UseDefaultCredentials = false;
                //账户，密码
                smtp.Credentials = new System.Net.NetworkCredential("jianwei_rong@promisingEDU.com", Common_SXD.pwd_jianwei_rong);
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;

                System.Net.Mail.MailMessage myMsg = new System.Net.Mail.MailMessage();
                string sTemp = Session["C_N_Employee"].ToString().Trim();
                if (sTemp == "戎建伟")
                    myMsg.From = new MailAddress("jianwei_rong@promisingEDU.com", "戎建伟", Encoding.GetEncoding("GB2312"));
                else
                    myMsg.From = new MailAddress("jianwei_rong@promisingEDU.com", "戎建伟 / " + sTemp, Encoding.GetEncoding("GB2312"));

                //myMsg.Charset = "gb2312";
                myMsg.SubjectEncoding = Encoding.GetEncoding("GB2312");
                myMsg.BodyEncoding = Encoding.GetEncoding("GB2312");

                Regex Is_IP = new Regex("([0-9]{1,3}(.)[0-9]{1,3}(.)[0-9]{1,3}(.)[0-9]{1,3})");

                string sIP = TextBox_IP_Beijing.Text.ToString().Trim();
                if (Is_IP.IsMatch(sIP))
                {

                    myMsg.Subject = "http://" + sIP + "/ukeasbj/";
                    myMsg.Body = "外地办公室请通过 http://" + sIP + "/ukeasbj/ 登录 Green Database";
                    Response.Clear();
                }
                else
                {
                    Response.Write("<li>IP地址格式错误！不发送电子邮件！</li>");
                    return;
                }

                MakeListRecipient(myMsg);

                smtp.Send(myMsg);
            }
            catch (SmtpException se)
            {
                Response.Write("<li>smtp error.</li>" + se.Message);
                return;
            }
            catch (System.Exception e)
            {
                Response.Write("<li>system error.</li>" + e.Message);
                return;
            }

            Response.Write("<li>电子邮件发送成功！</li>");
        }

        /// <summary>
        /// 添加电子邮件收件人列表
        /// </summary>
        public void MakeListRecipient(System.Net.Mail.MailMessage myMsg)
        {
            ////周一、周五发送给蔡老师
            ///if ((System.DateTime.Now.DayOfWeek == DayOfWeek.Monday) || (System.DateTime.Now.DayOfWeek == DayOfWeek.Friday))
                myMsg.To.Add(new MailAddress("tina@promisingEDU.com", "", Encoding.GetEncoding("GB2312")));

            ///myMsg.Bcc.Add(new MailAddress("rongjianwei@sina.com", "", Encoding.GetEncoding("GB2312")));
            myMsg.To.Add(new MailAddress("jianwei_rong@promisingEDU.com", "", Encoding.GetEncoding("GB2312")));

            string sTemp = Session["C_N_Employee"].ToString().Trim();
            if (sTemp != "戎建伟")
                ///myMsg.To.Add(new MailAddress(Session["Email"].ToString().Trim() + "@promisingEDU.com", "", Encoding.GetEncoding("GB2312")));
                myMsg.To.Add(new MailAddress(Common_SXD.eMail_Replacement( Session["Email"].ToString().Trim()),
                    "", Encoding.GetEncoding("GB2312")));


            /////////////////////////////////////
            ///从员工信息表生成，邮件接收人
            ////////////////////////////////////

            string sSql = "select Email, C_N_Employee, Office from Employees where";
            sSql += "( ( C_N_Employee not like '?' and Office  not like '北京' and Office  not like '上海' and ( Is_O_M = 1 or Is_Collector = 1  ) ) or";
            sSql += "(  C_N_Employee not like '?'and Office  like '上海' and Email not like 'tina' ) ) ";
            sSql += " and Is_Active = 1  ";
            sSql += " order by Office, Code_Employee";

#if MSSql           
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
            
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;
            ///Regex StandEmail = new Regex("(@)");

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                String sEmail = sqlReader.GetValue(0).ToString();
                String sOffice = sqlReader.GetValue(2).ToString();
                /*
                if (StandEmail.IsMatch(sEmail))
                    myMsg.Bcc.Add(new MailAddress(sEmail, "", Encoding.GetEncoding("GB2312")));
                else
                    myMsg.Bcc.Add(new MailAddress(sEmail + "@promisingEDU.com", "", Encoding.GetEncoding("GB2312")));
                */
                myMsg.Bcc.Add(new MailAddress(Common_SXD.eMail_Replacement( sEmail ), "", Encoding.GetEncoding("GB2312")));

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql

            ///打开数据库，获取sqlReader变量
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;
            ///Regex StandEmail = new Regex("(@)");

            // 依次读出数据
            while (MysqlReader.Read())
            {
                String sEmail = MysqlReader.GetValue(0).ToString();
                String sOffice = MysqlReader.GetValue(2).ToString();
                /*
                if (StandEmail.IsMatch(sEmail))
                    myMsg.Bcc.Add(new MailAddress(sEmail, "", Encoding.GetEncoding("GB2312")));
                else
                    myMsg.Bcc.Add(new MailAddress(sEmail + "@promisingEDU.com", "", Encoding.GetEncoding("GB2312")));
                */
                myMsg.Bcc.Add(new MailAddress(Common_SXD.eMail_Replacement( sEmail ), "", Encoding.GetEncoding("GB2312")));

            }
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
#endif
        }

        protected void Bt_IP_Beijing_Click(object sender, EventArgs e)
        {
            Sendmail_IP_BJ();
        }

        private object m_objOpt = System.Reflection.Missing.Value;
        protected void Button_Excel_Click(object sender, EventArgs e)
        {
           
            
            //创建Excel文件

            try
            {
                CreateExcelTable();
                Response.Write("<script>alert('成功导出!')</script>");
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                ///Response.Write("<script>alert('发生错误!')</script>");
            }
        }
        //////////////////////////////////////////


        ///public partial class MyExcelToDG : System.Web.UI.Page
        ///{
            private readonly string SQLCONNECTIONSTRING = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"].ToString();
            private readonly string DBPATH = ConfigurationManager.AppSettings["DBPATH"].ToString();
        
            private DataSet CreateDataSource()
            {
                
                //设置Excel的文件访问地址
                String ExcelDBPath = SQLCONNECTIONSTRING +  Server.MapPath(DBPATH) + ";";
                
                //定义访问Excel文件的连接
                OleDbConnection conn = new OleDbConnection(ExcelDBPath);
                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [students$]", conn);
                //OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM students", conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        ///}



        /// <summary>
        /// /////////////////////
        /// 
        /// </summary>
        ///public partial class MyDataToExcel : System.Web.UI.Page
        ///{
            private readonly string SQLCONNECTIONSTR = ConfigurationManager.AppSettings["SQLCONNECTIONSTR"].ToString();
            
    /*
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    //创建Excel文件

                    try
                    {
                        CreateExcelTable();
                        Response.Write("<script>alert('成功导出!')</script>");
                    }
                    catch (Exception)
                    {
                        Response.Write("<script>alert('发生错误!')</script>");
                    }
                }
            }*/
            //获取数据
            private DataSet GetData()
            {
                //从数据库中获取数据
                String cmdText = "Select * from Employees ";
                using (SqlConnection conn = new SqlConnection(SQLCONNECTIONSTR))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
                    conn.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return (ds);
                }
            }

            //创建Excel文件
            private void CreateExcelTable1()
            {
                //从数据库获取数据
                DataSet ds = GetData();
                //创建Excel对象
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                //设置行和列的索引
                int rowIndex = 1;
                int collndex = 0;
                //添加Excel对象的WorkBooks
                excel.Application.Workbooks.Add(true);
                System.Data.DataTable table = ds.Tables[0];
                //将所得到的表的列名，赋给单元格
                foreach (DataColumn col in table.Columns)
                {
                    //添加列名
                    collndex++;
                    excel.Cells[1, collndex] = col.ColumnName;
                }
                //同样的方法处理数据
                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    collndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        collndex++;
                        excel.Cells[rowIndex, collndex] = row[col.ColumnName].ToString();
                    }
                }
                //不可见，即后台处理
                excel.Visible = false;
                excel.DisplayAlerts = false;
                //保存刚才创建的Excel表格
                excel.Save(MapPath("App_Data/ExcelTable.xls"));

                excel.Application.Workbooks.Close();
                excel.Application.Quit();
                excel.Quit();
                //释放使用的Excel对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                GC.Collect();
            }

            //创建Excel文件
            private void CreateExcelTable()
            {
                Microsoft.Office.Interop.Excel.Application app1 = new Microsoft.Office.Interop.Excel.Application();
                app1.Visible = false;

                Excel.Workbook book1 = app1.Workbooks.Add(Type.Missing);

                Excel.Worksheet sheet1 = book1.Sheets[1];

                Excel.Range rng1 = sheet1.get_Range("A1", Type.Missing);
                rng1.Value2 = "hello";
                string temp = MapPath("App_Data/ExcelTable.xls");
                //判断文件是不是存在
                if(File.Exists(temp))
                {
                    //如果存在则删除
                    File.Delete(temp);
                }
                book1.SaveAs(temp, m_objOpt, m_objOpt,
                      m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                      m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

                app1.Application.Workbooks.Close();
                app1.Application.Quit();
                app1.Quit();
                //释放使用的Excel对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app1);
                GC.Collect();
            }
        ///}


        /////////////////////////


       /// public Excel.Workbook book1 { get; set; }
    }
}
